/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package EntityDB;

import entity.Order;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author NgocNam
 */
public class orderDB {
   private String URL = "jdbc:sqlserver://localhost;databaseName=LeaveManagerSystem";
   private String Use = "sa"; 
   private String Pass = "123456";
   private Connection conn;
   
    public orderDB(){
        try {
            conn=DriverManager.getConnection(URL,Use,Pass);
        } catch (SQLException ex) {
            Logger.getLogger(orderDB.class.getName()).log(Level.SEVERE, null, ex);
        }
       
    }
    
    public boolean addOrder(Order o){
        try {
            CallableStatement csmt=conn.prepareCall("{call insertOrder(?,?,?,?,?,?,?,?)}");
                 csmt.registerOutParameter(1, Types.INTEGER);
                 csmt.setInt(2, o.getEmployeeID());
                 csmt.setDate(3, new java.sql.Date(o.getStratDate().getTime()));
                 csmt.setDate(4, new java.sql.Date(o.getEndDate().getTime()));
                 csmt.setString(5, o.getReasons());
                 csmt.setInt(6, o.getStatus());
                 csmt.setDate(7, new java.sql.Date(o.getDateBackFact().getTime()));
                 csmt.setString(8, o.getResons_Delay());
                 csmt.executeUpdate();
                 int orderID=csmt.getInt(1);
                 csmt.setInt(1, orderID);
                 return true;
        } catch (SQLException ex) {
            Logger.getLogger(orderDB.class.getName()).log(Level.SEVERE, null, ex);
        }
               return false; 
    }
    public boolean updateOrder(Order o){
        try {
            CallableStatement csmt=conn.prepareCall("{call updateOrder(?,?,?,?,?,?,?,?)}");
            csmt.setInt(1, o.getOrderID());
            csmt.setInt(2, o.getEmployeeID());
            csmt.setDate(3,new java.sql.Date(o.getStratDate().getTime()));
            csmt.setDate(4, new java.sql.Date(o.getEndDate().getTime()));
            csmt.setString(5, o.getReasons());
            csmt.setInt(6, o.getStatus());
            csmt.setDate(7, new java.sql.Date(o.getDateBackFact().getTime()));
            csmt.setString(8, o.getResons_Delay());
            int row=csmt.executeUpdate();
            if(row>0){
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(orderDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
    }
    
    public boolean  removeOrderID(int orderID){
        try {
            CallableStatement csmt=conn.prepareCall("{call removeOrder(?)}");
            csmt.setInt(1, orderID);
            int row=csmt.executeUpdate();
            if(row>0){
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(orderDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    public ArrayList<Order> getOrder(){
        ArrayList<Order> list=new ArrayList<Order>();
        
        try {
            CallableStatement csmt=conn.prepareCall("{call getOrder}");
            ResultSet rs=csmt.executeQuery();
            while(rs.next()){
                int orderID=rs.getInt("OrderID");
                int employeeID=rs.getInt("EmployeeID");
                Date stratDate=rs.getDate("StartDate");
                Date endDate=rs.getDate("EndDate");
                String resons=rs.getString("Reasons");
                int status=rs.getInt("Status");
                Date DateBackFact=rs.getDate("DateBack_Fact");
                String resonsFact=rs.getString("Reasosn_delay");
                Order o=new Order(orderID, employeeID, stratDate, endDate, resons, status, DateBackFact, resonsFact);
                list.add(o);
                
            }
        } catch (SQLException ex) {
             Logger.getLogger(orderDB.class.getName()).log(Level.SEVERE, null, ex);
        }
//        System.out.println(list.size());
            return list;
    }
}
